## A.3. The DBI Class

In this section, we cover the DBI class methods, utility functions, and the dynamic attributes associated with generic DBI handles.

### A.3.1. DBI Class Methods

The following methods are provided by the DBI class:

connect |

    $dbh = DBI->connect($data_source, $username, $password)
              || die $DBI::errstr;
    $dbh = DBI->connect($data_source, $username, $password, \%attr)
              || die $DBI::errstr;

`connect` establishes a database connection, or session, to the requested `$data_source` . Returns a database handle object if the connection succeeds. Use `$dbh-`>`disconnect` to terminate the connection.

If the connect fails (see below), it returns `undef` and sets both `$DBI::err` and `$DBI::errstr`. (It does not set `$!`, etc.) You should generally test the return status of `connect` and `print $DBI::errstr` if it has failed.

Multiple simultaneous connections to multiple databases through multiple drivers can be made via the DBI. Simply make one `connect` call for each database and keep a copy of each returned database handle.

The `$data_source` value should begin with `dbi:`driver_name`:`. The driver_name specifies the driver that will be used to make the connection. (Letter case is significant.)

As a convenience, if the `$data_source` parameter is undefined or empty, the DBI will substitute the value of the environment variable `DBI_DSN`. If just the driver_name part is empty (i.e., the `$data_source` prefix is `dbi::`), the environment variable `DBI_DRIVER` is used. If neither variable is set, then `connect` dies.

Examples of `$data_source` values are:

>     dbi:DriverName:database_name
>     dbi:DriverName:database_name@hostname:port
>     dbi:DriverName:database=database_name;host=hostname;port=port

There is no standard for the text following the driver name. Each driver is free to use whatever syntax it wants. The only requirement the DBI makes is that all the information is supplied in a single string. You must consult the documentation for the drivers you are using for a description of the syntax they require. (Where a driver author needs to define a syntax for the `$data_source`, it is recommended that he or she follow the ODBC style, shown in the last example above.)

If the environment variable `DBI_AUTOPROXY` is defined (and the driver in `$data_source` is not `Proxy`) then the connect request will automatically be changed to:

>     dbi:Proxy:$ENV{DBI_AUTOPROXY};dsn=$data_source

and passed to the `DBD::Proxy` module. `DBI_AUTOPROXY` is typically set as "`hostname=...;port=...`". See the `DBD::Proxy` documentation for more details.

If `$username` or `$password` are undefined (rather than just empty), then the DBI will substitute the values of the `DBI_USER` and `DBI_PASS` environment variables, respectively. The DBI will warn if the environment variables are not defined. However, the everyday use of these environment variables is not recommended for security reasons. The mechanism is primarily intended to simplify testing.

`DBI-`>`connect` automatically installs the driver if it has not been installed yet. Driver installation either returns a valid driver handle, or it dies with an error message that includes the string `install_driver` and the underlying problem. So `DBI-`>`connect` will die on a driver installation failure and will only return `undef` on a connect failure, in which case `$DBI::errstr` will hold the error message.

The `$data_source` argument (with the "`dbi:...:`" prefix removed) and the `$username` and `$password` arguments are then passed to the driver for processing. The DBI does not define any interpretation for the contents of these fields. The driver is free to interpret the `$data_source`, `$username`, and `$password` fields in any way, and supply whatever defaults are appropriate for the engine being accessed. (Oracle, for example, uses the ORACLE_SID and TWO_TASK environment variables if no `$data_source` is specified.)

The `AutoCommit` and `PrintError` attributes for each connection default to "on." (See `AutoCommit` and `PrintError` for more information.) However, it is strongly recommended that you explicitly define `AutoCommit` rather than rely on the default. Future versions of the DBI may issue a warning if `AutoCommit` is not explicitly defined.

The `\%attr` parameter can be used to alter the default settings of `PrintError`, `RaiseError` , `AutoCommit`, and other attributes. For example:

>     $dbh = DBI->connect($data_source, $user, $pass, {
>           PrintError => 0,
>           AutoCommit => 0
>     });

You can also define connection attribute values within the `$data_source` parameter. For example:

>     dbi:DriverName(PrintError=>0,Taint=>1):...

Individual attribute values specified in this way take precedence over any conflicting values specified via the `\%attr` parameter to `connect`.

Where possible, each session (`$dbh` ) is independent from the transactions in other sessions. This is useful when you need to hold cursors open across transactions -- for example, if you use one session for your long lifespan cursors (typically read-only) and another for your short update transactions.

For compatibility with old DBI scripts, the driver can be specified by passing its name as the fourth argument to `connect` (instead of `\%attr`):

>     $dbh = DBI->connect($data_source, $user, $pass, $driver);

In this "old-style" form of `connect`, the `$data_source` should not start with `dbi:`driver_name`:`. (If it does, the embedded driver_name will be ignored). Also note that in this older form of `connect`, the `$dbh-`>`{AutoCommit}` attribute is undefined, the `$dbh-`>`{PrintError}` attribute is off, and the old `DBI_DBNAME` environment variable is checked if `DBI_DSN` is not defined. Beware that this "old-style" `connect` will be withdrawn in a future version of DBI.

connect_cached (NEW ) |

    $dbh = DBI->connect_cached($data_source, $username, $password)
              || die $DBI::errstr;
    $dbh = DBI->connect_cached($data_source, $username, $password, \%attr)
              || die $DBI::errstr;

`connect_cached` is like connect, except that the database handle returned is also stored in a hash associated with the given parameters. If another call is made to `connect_cached` with the same parameter values, then the corresponding cached `$dbh` will be returned if it is still valid. The cached database handle is replaced with a new connection if it has been disconnected or if the `ping` method fails.

Note that the behavior of this method differs in several respects from the behavior of persistent connections implemented by `Apache::DBI`.

Caching can be useful in some applications, but it can also cause problems and should be used with care. The exact behavior of this method is liable to change, so if you intend to use it in any production applications you should discuss your needs on the dbi-users mailing list.

The cache can be accessed (and cleared) via the `CachedKids` attribute.

available_drivers |

    @ary = DBI->available_drivers;
    @ary = DBI->available_drivers($quiet);

Returns a list of all available drivers by searching for `DBD::*` modules through the directories in `@INC`. By default, a warning is given if some drivers are hidden by others of the same name in earlier directories. Passing a true value for `$quiet` will inhibit the warning.

data_sources |

    @ary = DBI->data_sources($driver);
    @ary = DBI->data_sources($driver, \%attr);

Returns a list of all data sources (databases) available via the named driver. The driver will be loaded if it hasn't been already. If `$driver` is empty or `undef`, then the value of the `DBI_DRIVER` environment variable is used.

Data sources are returned in a form suitable for passing to the `connect` method (that is, they will include the `dbi:$driver:` prefix).

Note that many drivers have no way of knowing what data sources might be available. These drivers return an empty or incomplete list.

trace |

    DBI->trace($trace_level)
    DBI->trace($trace_level, $trace_filename)

DBI trace information can be enabled for all handles using the `trace` DBI class method. To enable trace information for a specific handle, use the similar `$h-`>`trace` method described elsewhere.

Trace levels are as follows:

0

  Trace disabled.

1

  Trace DBI method calls returning with results or errors.

2

  Trace method entry with parameters and returning with results.

3

  As above, adding some high-level information from the driver and some internal information from the DBI.

4

  As above, adding more detailed information from the driver. Also includes DBI mutex information when using threaded Perl.

5 and above

  As above, but with more and more obscure information.

Trace level 1 is best for a simple overview of what's happening. Trace level 2 is a good choice for general purpose tracing. Levels 3 and above (up to 9) are best reserved for investigating a specific problem, when you need to see "inside" the driver and DBI.

The trace output is detailed and typically very useful. Much of the trace output is formatted using the `neat` function, so strings may be edited and truncated.

Initially, trace output is written to `STDERR`. If `$trace_filename` is specified, the file is opened in append mode and all trace output (including that from other handles) is redirected to that file. Further calls to `trace` without a `$trace_filename` do not alter where the trace output is sent. If `$trace_filename` is undefined, then trace output is sent to `STDERR` and the previous trace file is closed.

See also the `$h-`>`trace` and `$h-`>`trace_msg` methods for information about the `DBI_TRACE` environment variable.

### A.3.2. DBI Utility Functions

In addition to the methods listed in the previous section, the DBI package also provides the following utility functions:

neat |

    $str = DBI::neat($value, $maxlen);

Returns a string containing a neat (and tidy) representation of the supplied value.

Strings will be quoted, although internal quotes will not be escaped. Values known to be numeric will be unquoted. Undefined (NULL) values will be shown as `undef` (without quotes). Unprintable characters will be replaced by a dot (.).

For result strings longer than `$maxlen`, the result string will be truncated to `$maxlen-4`, and "`...'`" will be appended. If `$maxlen` is `0` or `undef`, it defaults to `$DBI::neat_maxlen`, which, in turn, defaults to `400`.

This function is designed to format values for human consumption. It is used internally by the DBI for `trace` output. It should typically not be used for formatting values for database use. (See also `quote`.)

neat_list |

    $str = DBI::neat_list(\@listref, $maxlen, $field_sep);

Calls `DBI::neat` on each element of the list and returns a string containing the results joined with `$field_sep`. `$field_sep` defaults to `", "`.

looks_like_number |

    @bool = DBI::looks_like_number(@array);

Returns true for each element that looks like a number. Returns false for each element that does not look like a number. Returns `undef` for each element that is undefined or empty.

### A.3.3. DBI Dynamic Attributes

Dynamic attributes are always associated with the last handle used (that handle is represented by `$h` in the descriptions below).

Where an attribute is equivalent to a method call, refer to the method call for all related documentation.

Warning: these attributes are provided as a convenience, but they do have limitations. Specifically, they have a short lifespan: because they are associated with the last handle used, they should be used only immediately after calling the method that "sets" them. If in any doubt, use the corresponding method call.

$DBI::err

  Equivalent to `$h-`>`err`.

$DBI::errstr

  Equivalent to `$h-`>`errstr`.

$DBI::state

  Equivalent to `$h-`>`state`.

$DBI::rows

  Equivalent to `$h-`>`rows`. Please refer to the documentation for the `rows` method.

Copyright © 2001 O'Reilly & Associates. All rights reserved.